package com.huawei.codearts.dao;

import com.huawei.codearts.model.Order;
import com.huawei.codearts.rowmapper.OrderRowMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

@Repository
public class OrderDao {

    @Autowired
    private NamedParameterJdbcTemplate namedJdbcTemplate;  // 占位符 :变量名

//    @Autowired
    private final JdbcTemplate jdbcTemplate;  // 普通方式 ?占位符

    public OrderDao(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    /**
     * 单条数据查询 带占位符式的sql
     * @return Object
     */
    public Order getOneId(Object[] args) {

        // 获取int类型
        String sql11 = "select count(*) from qhdzs_orders";
        int count = jdbcTemplate.queryForObject(sql11, Integer.class);  //第二个参数是返回类型的class
        System.out.println("getOneId...userCount..." +count);

        String sql22 = "select count(*) from qhdzs_orders where id = ?";
        Object[] params = args;
        int count0 = jdbcTemplate.queryForObject(sql22, params, Integer.class);
        System.out.println("getOneId...userCount..." +count0);

        // map类型数据 查询多个字段，返回一个Map
        String sql33 = "SELECT id, number, sort, member_id, membername, price, createtime FROM qhdzs_orders where id=?";
        Map<String, Object> resultMap = jdbcTemplate.queryForMap(sql33, args);
        System.out.println("getOneId...resultMap..." +resultMap);

        // 查询多条数据，每条数据都以Map形式返回，整个结果是List<Map<String, Object>>
        String sql44 = "SELECT id, number, sort, member_id, membername, price, createtime FROM qhdzs_orders where id=?";
        List<Map<String, Object>> resultMapList = jdbcTemplate.queryForList(sql44, params);
        System.out.println("getOneId...resultMapList..." +resultMapList);

        // 查询多条数据，返回实体类集合List<Order，通过BeanPropertyRowMapper自动映射实体属性
        String querySQL = "select * from qhdzs_orders where  id = ?";
        List<Order> orders = Collections.singletonList(jdbcTemplate.queryForObject(querySQL, new BeanPropertyRowMapper<>(Order.class), args));
        System.out.println("getOneId...orders..." +orders);

        // 查询单条数据，返回实体类对象，通过BeanPropertyRowMapper自动映射实体属性
        String querySQL2 = "select * from qhdzs_orders where  id = ?";
        List<Order> orders2 = jdbcTemplate.query(querySQL2, args, BeanPropertyRowMapper.newInstance(Order.class));
        System.out.println("getOneId...orders2..." +orders2);

        // 查询单条数据并通过RowMapper手动映射成实体对象
        String sqlOrder = "SELECT id, number, sort, member_id, membername, price, createtime FROM qhdzs_orders where id=?";
        Order orderModel = jdbcTemplate.queryForObject(sqlOrder, args,
                new RowMapper<Order>() {
                    @Override
                    public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
                        // 在这里做字段到实体的映射
                        Order o = new Order();
                        o.setId(rs.getInt("id"));
                        o.setNumber(rs.getString("number"));
                        o.setSort(rs.getString("sort"));
                        o.setMemberId(rs.getInt("member_id"));
                        o.setMemberName(rs.getString("membername"));
                        o.setPrice(rs.getBigDecimal("price"));
                        o.setCreateTime(rs.getLong("createtime"));
                        return o;
                    }
                });
        System.out.println("getOneId...orderModel..." +orderModel);

        // 查询对象集合
        String sqlOrderList = "SELECT id, number, sort, member_id, membername, price, createtime FROM qhdzs_orders where id=?";
        List<Order> orderModels = jdbcTemplate.query(sqlOrderList, args, new OrderRowMapper());
        System.out.println("getOneId...orderModels..." +orderModels);

        // 通过 :变量名 的占位符
        List<String> stringList = List.of("222", "223");
        List<Long> longList = stringList.stream().map(Long::parseLong).toList();
        String sql = "update qhdzs_orders set sort = :sort where id in (:ids)";
        Map<String, Object> paramsMap = new ConcurrentHashMap<>();
        paramsMap.put("ids", longList);
        paramsMap.put("sort", "00001");
        int successCount = namedJdbcTemplate.update(sql, paramsMap);
        System.out.println("getOneId...successCount..." +successCount);

        // 通过 :变量名 的占位符
        String sqlTotal = "select count(*) from qhdzs_orders where membername = :membername and sort = :sort";
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("membername", "aaa");
        paramMap.put("sort", "00001");
        Integer total = namedJdbcTemplate.queryForObject(sqlTotal, paramMap, Integer.class);
        System.out.println("getOneId...total..." +total);

        return orderModel;
    }

    /**
     * 多条数据查询
     * @return Object
     */
    public List<Order> getList(Object[] args) {

        String sql = "SELECT id, number, sort, member_id, membername, price, createtime FROM qhdzs_orders where id=? OR id=?";
        List<Order> dataList = jdbcTemplate.query(sql, new OrderRowMapper(), args);
        return dataList;
    }

    /**
     * 添加操作
     * @return boolean
     * https://blog.csdn.net/qq_45297578/article/details/128927759
     */
    public Integer insert(Order order) {
        String sql = "insert into qhdzs_orders (number, sort, member_id, membername, price, createtime) values(?, ?, ?, ?, ?, ?)";
        // KeyHolder保存返回的数据，可以指定返回的列
        KeyHolder keyHolder = new GeneratedKeyHolder();
        // rowsAffected 影响的行数
        int rowsAffected = jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                // 指定返回的属性列名为id
                PreparedStatement ps = conn.prepareStatement(sql, new String[]{"id", "number"});
                // 插入的数据
                ps.setString(1, order.getNumber());
                ps.setString(2,order.getSort());
                ps.setInt(3, order.getMemberId());
                ps.setString(4, order.getMemberName());
                ps.setBigDecimal(5, order.getPrice());
                ps.setLong(6, order.getCreateTime());
//                ps.setTimestamp(6, order.getCreateTime());
                return ps;

//                PreparedStatement ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
//                ps.setString(1, data);
//                return ps;
            }
        }, keyHolder);

        /** 单条插入 */
//        String sql = "insert into qhdzs_orders(user_id, username, mobile, create_time) values (?, ?, ?, ?)";
//        Object[] params = new Object[]{uuid(), "新用户", "134578546", new Date()};
//        int successCount = jdbcTemplate.update(sql, params);

        /** 批量插入 */
//        String sql = "insert into qhdzs_orders(user_id, username, mobile, create_time) values (?, ?, ?, ?)";
//        List<Object[]> params = new ArrayList<>();
//        params.add(new Object[]{uuid(), "新用户1", "134578546", new Date()});
//        params.add(new Object[]{uuid(), "新用户2", "134578546", new Date()});
//        int[] successCounts = jdbcTemplate.batchUpdate(sql, params);

        // 输出生成的uuid
        System.out.println("rowsAffected..."+rowsAffected);  // 影响的行数
        System.out.println("keyHolder..."+ Objects.requireNonNull(keyHolder.getKeys()).get("id"));  // ??为啥不好使
        System.out.println("keyHolder..."+ keyHolder.getKeys().get("number"));
        System.out.println("keyHolder..."+ keyHolder.getKey());
        int generatedId = Objects.requireNonNull(keyHolder.getKey()).intValue();  // 自增id
        System.out.println("generatedId..."+generatedId);
        return generatedId;
    }

    /**
     * 修改操作
     * @return boolean
     * https://blog.csdn.net/qq_45297578/article/details/128927759
     */
    public Integer update(Order order) {
        // 单条修更新
        String sql = "update qhdzs_orders set membername = ?, price = ? where id = ?";
        Object[] params = new Object[]{order.getMemberName(), order.getPrice(), order.getId()};
        int successCount = jdbcTemplate.update(sql, params);

        // 批量更新
//        String sql = "update qhdzs_orders set membername = ?, price = ? where id = ?";
//        List<Object[]> params = new ArrayList<>();
//        params.add(new Object[]{"更新用户名1", "0ff5771760c1483abaefa7a3e530ea6d"});
//        params.add(new Object[]{"更新用户名2", "4e88b38660324334be61f0c9b2edd7f6"});
//        int[] successCounts = jdbcTemplate.batchUpdate(sql, params);

        return successCount;
    }

    /**
     * 删除操作
     * @param id int
     * @return boolean
     */
    public Integer del(int id) {
        String sql = "delete from qhdzs_orders where id = ?";
        Object[] params = new Object[]{id};
        int successCount = jdbcTemplate.update(sql, params);
        return successCount;
    }

    /**
     * 单条数据查询
     * @return Object
     */
    public Order getOneTx(int id) {
        Object[] params = new Object[]{id};
//        String sqlOrder = "SELECT id, number, sort, member_id, membername, price, createtime FROM qhdzs_orders where id=? FOR UPDATE";
        String sqlOrder = "SELECT id, number, sort, member_id, membername, price, createtime FROM qhdzs_orders where id=?";
        Order orderModel = jdbcTemplate.queryForObject(sqlOrder, new OrderRowMapper(), params);
        System.out.println("getOneTx...orderModel..." +orderModel);
        return orderModel;
    }

    /**
     * 数据更新
     * @return Object
     */
    public int updateWithTx(String sql, Order order) {
//        String sql = "update qhdzs_orders set price = ? where id = ?";
        Object[] params = new Object[]{order.getPrice(), order.getId()};
        return jdbcTemplate.update(sql, params);
    }

}
